package com.ph.irp.base.usermanage.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.usermanage.dao.PBasePurviewFunctionroleDAO;
import com.ph.util.DBUtil;

public class PBasePurviewFunctionroleDAOImpl implements
		PBasePurviewFunctionroleDAO {
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public List<Integer> getFunctionByRoleID(String roleID) throws Throwable{
		List<Integer> resList = new ArrayList<Integer>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select f.FunctionTypeID fid ");
			sb.append("from p_base_purview_functionrole f ");
			sb.append("where f.RoleID=").append(Integer.parseInt(roleID));
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				resList.add(rs.getInt("fid"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;

	}
	
	public List<Integer> getRoleIDsByFunctionTypeID(int functionTypeID)throws Throwable{
		List<Integer> resList = new ArrayList<Integer>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select f.RoleID fid ");
			sb.append("from p_base_purview_functionrole f ");
			sb.append("where f.FunctionTypeID=").append(functionTypeID);
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				resList.add(rs.getInt("fid"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;

	
		
	}
	
	public List<Integer> getAllFunctionTypeID()throws Throwable{
		List<Integer> resList = new ArrayList<Integer>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select f.FunctionTypeID fid ");
			sb.append("from p_base_purview_functionrole f ");			
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				resList.add(rs.getInt("fid"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}
	
	public void addFunctionRole(String functionTypeID,String editUserGroupID)throws Throwable{
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb
					.append(" insert into p_base_purview_functionrole(FunctionTypeID,RoleID)");
			sb.append(" values(");
			sb.append(Integer.parseInt(functionTypeID));
			sb.append(",").append(Integer.parseInt(editUserGroupID));			
			sb.append(")");

			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	
		
	}
	
	public void deleteFunctionRoleByRoleID(String editUserGroupID)throws Throwable{

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("delete from p_base_purview_functionrole ");
			sb.append("where RoleID=").append(Integer.parseInt(editUserGroupID));

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		}catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	
	}
}
